CREATE TABLE address (
	id SERIAL PRIMARY KEY,
	pid INTEGER REFERENCES address(id),
	name TEXT NOT NULL
);

CREATE TABLE user_type (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL UNIQUE
);
INSERT INTO user_type (name) VALUES
('临时用户'),('商城用户'),('系统用户');

CREATE TABLE permission (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL UNIQUE,
	description TEXT
);
INSERT INTO permission (name, description) VALUES
('create_room', '创建房间'),
('delete_room', '删除任何房间'),
('remove_room', '删除拥有的房间'),
('move_room', '转移房间');
INSERT INTO permission (name, description) VALUES
('invite_user', '邀请用户'),
('kick_user', '踢出用户'),
('forbid_user', '禁言');

CREATE TABLE roles (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL UNIQUE
);
INSERT INTO roles (name) VALUES
('超级管理员'),('管理员'),('主持人');

CREATE TABLE role_permission (
	pid INTEGER REFERENCES permission(id),
	rid INTEGER REFERENCES roles(id),
	PRIMARY KEY(pid, rid)
);
INSERT INTO role_permission VALUES
((SELECT id FROM permission WHERE name = 'create_room'),
 (SELECT id FROM roles WHERE name = '超级管理员')),
((SELECT id FROM permission WHERE name = 'delete_room'),
 (SELECT id FROM roles WHERE name = '超级管理员')),
((SELECT id FROM permission WHERE name = 'move_room'),
 (SELECT id FROM roles WHERE name = '超级管理员')),
((SELECT id FROM permission WHERE name = 'remove_room'),
 (SELECT id FROM roles WHERE name = '管理员')),
((SELECT id FROM permission WHERE name = 'move_room'),
 (SELECT id FROM roles WHERE name = '管理员')),
((SELECT id FROM permission WHERE name = 'invite_user'),
 (SELECT id FROM roles WHERE name = '主持人')),
((SELECT id FROM permission WHERE name = 'kick_user'),
 (SELECT id FROM roles WHERE name = '主持人')),
((SELECT id FROM permission WHERE name = 'forbid_user'),
 (SELECT id FROM roles WHERE name = '主持人'));

CREATE TABLE users (
	id SERIAL PRIMARY KEY,
	type INTEGER REFERENCES user_type(id),
	name TEXT NOT NULL,
	alias TEXT UNIQUE,
	password TEXT,
	salt TEXT,
	created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_time TIMESTAMP,
	logout_time TIMESTAMP,
	avatar TEXT,
	fingerprint TEXT,
	address INTEGER REFERENCES address(id),
	attrs JSONB,
	UNIQUE (type, name)
);
CREATE INDEX idx_user_name ON users USING HASH(name);
CREATE INDEX idx_user_fingerprint ON users USING HASH(fingerprint);
CREATE INDEX idx_user_created_time ON users(created_time);
CREATE INDEX idx_user_updated_time ON users(updated_time);
CREATE INDEX idx_user_logout_time ON users(logout_time);
INSERT INTO users (type, name, salt, password) VALUES
((SELECT id FROM user_type WHERE name = '系统用户')
,'admin'
,'ec06eef3d27769882e85a544706af03007f99a14a2d675b6e320492f1e97e76e'
,'86f6f8e55627b3e8940c0fcc06618d5767cb80006fc7980c3fb61bd8b70ee7bf');

CREATE TABLE user_role (
	uid INTEGER REFERENCES users(id),
	rid INTEGER REFERENCES roles(id),
	PRIMARY KEY (uid, rid)
);
INSERT INTO user_role (uid, rid) VALUES
((SELECT id FROM users WHERE name = 'admin'),
 (SELECT id FROM roles WHERE name = '超级管理员'));

CREATE TABLE room (
	id SERIAL PRIMARY KEY,
	name TEXT UNIQUE NOT NULL,
	created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	public BOOLEAN DEFAULT false
);
INSERT INTO room (name, public) VALUES ('public', true);

CREATE TABLE user_room (
	join_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	leave_time TIMESTAMP,
	uid INTEGER REFERENCES users(id),
	rid INTEGER REFERENCES room(id),
	PRIMARY KEY(uid, rid)
);
INSERT INTO user_room (uid, rid) VALUES
((SELECT id FROM users WHERE name = 'admin')
,(SELECT id FROM room WHERE name = 'public'));

CREATE TABLE user_room_role(
	uid INTEGER REFERENCES users(id),
	rid INTEGER REFERENCES room(id),
	oid INTEGER REFERENCES roles(id),
	PRIMARY KEY(uid, rid, oid)
);
INSERT INTO user_room_role (uid, rid, oid) VALUES
((SELECT id FROM users WHERE name = 'admin')
,(SELECT id FROM room WHERE name = 'public')
,(SELECT id FROM roles WHERE name = '超级管理员'))
,
((SELECT id FROM users WHERE name = 'admin')
,(SELECT id FROM room WHERE name = 'public')
,(SELECT id FROM roles WHERE name = '管理员'))
,
((SELECT id FROM users WHERE name = 'admin')
,(SELECT id FROM room WHERE name = 'public')
,(SELECT id FROM roles WHERE name = '主持人'));


CREATE TABLE message (
	id SERIAL PRIMARY KEY,
	created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	src INTEGER NOT NULL REFERENCES users(id),
	content JSONB
);
CREATE INDEX idx_message_created_time ON message(created_time);
CREATE INDEX idx_message_src ON message USING BTREE(src);

CREATE TABLE msg_user (
	dist INTEGER NOT NULL REFERENCES users(id)
) INHERITS (message);
CREATE INDEX idx_message_user_src ON msg_user USING BTREE(src);
CREATE INDEX idx_message_user_dist ON msg_user USING BTREE(dist);
CREATE INDEX idx_message_user_created ON msg_user USING BTREE(created_time);

CREATE TABLE msg_room (
	dist INTEGER NOT NULL REFERENCES room(id)
) INHERITS (message);
CREATE INDEX idx_message_room_src ON msg_room USING BTREE(src);
CREATE INDEX idx_message_room_dist ON msg_room USING BTREE(dist);
CREATE INDEX idx_message_room_created ON msg_room USING BTREE(created_time);